package cn.com.jdyun.mapper.ext;

import cn.com.jdyun.mapper.RefereeMapper;
import cn.com.jdyun.pojo.Referee;
import cn.com.jdyun.pojo.User;
import cn.com.jdyun.pojo.UserWallet;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.type.JdbcType;

import java.math.BigDecimal;
import java.util.List;
import java.util.Set;

public interface ExtRefereeMapper extends RefereeMapper {

    @Select({
            "select",
            "u.id, u.nick_name, u.head_pic, u.referee, u.code, ",
            "u.flag, u.admin, u.create_time, u.update_time",
            "from phone_user u",
            "left join phone_referee r on u.id = r.user_id",
            "where r.referee_id = #{userId,jdbcType=VARCHAR} "
    })
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.VARCHAR, id = true),
            @Result(column = "phone", property = "phone", jdbcType = JdbcType.VARCHAR),
            @Result(column = "email", property = "email", jdbcType = JdbcType.VARCHAR),
            @Result(column = "nick_name", property = "nickName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "head_pic", property = "headPic", jdbcType = JdbcType.VARCHAR),
            @Result(column = "referee", property = "referee", jdbcType = JdbcType.VARCHAR),
            @Result(column = "code", property = "code", jdbcType = JdbcType.VARCHAR),
            @Result(column = "flag", property = "flag", jdbcType = JdbcType.TINYINT),
            @Result(column = "admin", property = "admin", jdbcType = JdbcType.TINYINT),
            @Result(column = "create_time", property = "createTime", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "update_time", property = "updateTime", jdbcType = JdbcType.TIMESTAMP)
    })
    List<User> selectReferees(String userId);

    @Select({
            "select",
            "u.id, u.nick_name, u.head_pic, u.referee, u.code, ",
            "u.flag, u.admin, u.create_time, u.update_time",
            "from phone_user u",
            "left join phone_referee r on u.id = r.referee_id",
            "where r.user_id = #{userId,jdbcType=VARCHAR} limit 1"
    })
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.VARCHAR, id = true),
            @Result(column = "phone", property = "phone", jdbcType = JdbcType.VARCHAR),
            @Result(column = "email", property = "email", jdbcType = JdbcType.VARCHAR),
            @Result(column = "nick_name", property = "nickName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "head_pic", property = "headPic", jdbcType = JdbcType.VARCHAR),
            @Result(column = "referee", property = "referee", jdbcType = JdbcType.VARCHAR),
            @Result(column = "code", property = "code", jdbcType = JdbcType.VARCHAR),
            @Result(column = "flag", property = "flag", jdbcType = JdbcType.TINYINT),
            @Result(column = "admin", property = "admin", jdbcType = JdbcType.TINYINT),
            @Result(column = "create_time", property = "createTime", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "update_time", property = "updateTime", jdbcType = JdbcType.TIMESTAMP)
    })
    User selectReferee(String userId);

    @Select({
            "select",
            "id, user_id, referee_id, referee_parent_id, context, create_time, update_time",
            "from phone_referee",
            "where user_id = #{userId,jdbcType=VARCHAR}"
    })
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER, id = true),
            @Result(column = "user_id", property = "userId", jdbcType = JdbcType.VARCHAR),
            @Result(column = "referee_id", property = "refereeId", jdbcType = JdbcType.VARCHAR),
            @Result(column = "referee_parent_id", property = "refereeParentId", jdbcType = JdbcType.VARCHAR),
            @Result(column = "context", property = "context", jdbcType = JdbcType.VARCHAR),
            @Result(column = "create_time", property = "createTime", jdbcType = JdbcType.TIMESTAMP),
            @Result(column = "update_time", property = "updateTime", jdbcType = JdbcType.TIMESTAMP)
    })
    Referee selectUser(@Param("userId") String userId);

    @Select({
            "SELECT SUM(uw.locked_coin) FROM phone_referee r ",
            "LEFT JOIN phone_user_wallet uw ON r.user_id = uw.user_id",
            "WHERE (r.referee_id = #{userId,jdbcType=VARCHAR} OR r.referee_parent_id = #{userId,jdbcType=VARCHAR}) and uw.coin_type = #{coinType}",
    })
    BigDecimal queryAmount(@Param("userId") String userId, @Param("coinType") String coinType);

    @Select({
            "SELECT uw.id, uw.user_id, uw.coin_type, uw.asset, uw.balance, uw.locked_coin  FROM phone_referee r ",
            "LEFT JOIN phone_user_wallet uw ON r.user_id = uw.user_id",
            "WHERE (r.referee_id = #{userId,jdbcType=VARCHAR} OR r.referee_parent_id = #{userId,jdbcType=VARCHAR})  and uw.coin_type = #{coinType}",
    })
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER, id = true),
            @Result(column = "user_id", property = "userId", jdbcType = JdbcType.VARCHAR),
            @Result(column = "coin_type", property = "coinType", jdbcType = JdbcType.VARCHAR),
            @Result(column = "asset", property = "asset", jdbcType = JdbcType.VARCHAR),
            @Result(column = "balance", property = "balance", jdbcType = JdbcType.VARCHAR),
            @Result(column = "locked_coin", property = "lockedCoin", jdbcType = JdbcType.DECIMAL)
    })
    List<UserWallet> queryAllUserWallet(@Param("userId") String userId, @Param("coinType") String coinType);
    
    //节点自身至少是小矿机，节点邀请了三个以上至少是小矿机的节点，一二级节点的总锁仓达到超级节点限额，特意设置为具体节点的节点
    @Select({
        "SELECT r.user_id",
        "FROM phone_referee r ",
        "INNER JOIN phone_user u ON r.user_id = u.id and u.level = 0",
        "INNER JOIN phone_user_wallet uw ON (uw.coin_type = #{coinType} AND r.user_id = uw.user_id AND uw.locked_coin >= #{tinyLimit})",
        "LEFT JOIN phone_referee r1 ON (r1.referee_id = r.user_id OR r1.referee_parent_id = r.user_id)",
        "LEFT JOIN phone_user u1 ON r1.user_id = u1.id",
        "LEFT JOIN phone_user_wallet uw1 ON (uw1.coin_type = #{coinType} AND r1.user_id = uw1.user_id)",
        "WHERE uw.locked_coin IS NOT NULL ",
        "GROUP BY r.user_id",
        "HAVING SUM(uw1.locked_coin) >= #{superNodeLimit} AND COUNT(u1.flag = 0 and r1.referee_id = r.user_id AND uw1.locked_coin >= #{tinyLimit} OR u1.flag > 1 and r1.referee_id = r.user_id OR NULL) >= #{numLimit}"
    })
    Set<String> queryAllSuperNodeId(@Param("coinType") String coinType, @Param("tinyLimit") String tinyLimit, @Param("superNodeLimit") String superNodeLimit, @Param("numLimit") int numLimit);


    @Update("update phone_user set level = #{level} where id = #{userId}")
    int updateLevel(@Param("userId")String userId,@Param("level") int level);

    @Update("update phone_user set tex_level = #{texLevel} where id = #{userId}")
    int updateTexLevel(@Param("userId")String userId,@Param("texLevel") int texLevel);

    //节点自身至少是大矿机，节点邀请了三个超级节点
    @Select({
    	"<script>",
        "SELECT r.referee_id FROM phone_referee r",
        "INNER JOIN phone_user u ON r.referee_id = u.id and u.level = 0",
        "INNER JOIN phone_user_wallet uw ON (r.referee_id = uw.user_id)",
        "WHERE uw.coin_type = #{coinType} AND uw.locked_coin >= #{strongLimit} and r.user_id IN",
        "<foreach collection='superNodeIds' item='userId' open='(' separator=',' close=')'>",
        "#{userId}", 
        "</foreach>",
        "AND r.referee_id NOT IN",
        "<foreach collection='superNodeIds' item='userId' open='(' separator=',' close=')'>",
        "#{userId}", 
        "</foreach>",
        " GROUP BY r.referee_id",
        " HAVING COUNT(r.referee_id) >= #{numLimit}",
        "</script>"
    })
    Set<String> queryAllPartnerId(@Param("coinType") String coinType, @Param("superNodeIds") Set<String> superNodeIds, @Param("strongLimit") String strongLimit, @Param("numLimit") int numLimit);
    
    @Select({
            "select",
            "count(id) c",
            "from phone_referee",
            "where referee_id = #{userId,jdbcType=VARCHAR}"
    })
    int selectLevel1Amount(String userId);

    @Select({
            "select",
            "count(id) c",
            "from phone_referee",
            "where referee_parent_id = #{userId,jdbcType=VARCHAR}"
    })
    int selectLevel2Amount(String userId);
    
    @Select({
        "SELECT count(r.user_id) > 0",
        "FROM phone_referee r ",
        "INNER JOIN phone_user u ON r.user_id = u.id and u.level = 0",
        "INNER JOIN phone_user_wallet uw ON (uw.coin_type = #{coinType} AND r.user_id = uw.user_id AND uw.locked_coin >= #{tinyLimit})",
        "LEFT JOIN phone_referee r1 ON (r1.referee_id = r.user_id OR r1.referee_parent_id = r.user_id)",
        "LEFT JOIN phone_user u1 ON r1.user_id = u1.id",
        "LEFT JOIN phone_user_wallet uw1 ON (uw1.coin_type = #{coinType} AND r1.user_id = uw1.user_id)",
        "WHERE r.user_id = #{userId} and uw.locked_coin IS NOT NULL ",
        "GROUP BY r.user_id",
        "HAVING SUM(uw1.locked_coin) >= #{superNodeLimit} AND COUNT(u1.flag = 0 and r1.referee_id = r.user_id AND uw1.locked_coin >= #{tinyLimit} OR r1.referee_id = r.user_id AND u1.flag > 1 OR NULL) >= #{numLimit}"
    })
	Boolean isSuperNode(@Param("userId")String userId, @Param("coinType") String coinType, @Param("tinyLimit") String tinyLimit, @Param("superNodeLimit") String superNodeLimit, @Param("numLimit") int numLimit);
    
    @Select({
        "SELECT count(r.user_id) >= #{numLimit}",
        "FROM phone_referee r ",
        "INNER JOIN phone_user u ON r.user_id = u.id and u.level = 0",
        "INNER JOIN phone_user_wallet uw ON (uw.coin_type = #{coinType} AND r.user_id = uw.user_id AND uw.locked_coin >= #{tinyLimit})",
        "LEFT JOIN phone_referee r1 ON (r1.referee_id = r.user_id OR r1.referee_parent_id = r.user_id)",
        "LEFT JOIN phone_user u1 ON r1.user_id = u1.id",
        "LEFT JOIN phone_user_wallet uw1 ON (uw1.coin_type = #{coinType} AND r1.user_id = uw1.user_id)",
        "WHERE r.referee_id = #{userId} and uw.locked_coin IS NOT NULL ",
        "GROUP BY r.user_id",
        "HAVING SUM(uw1.locked_coin) >= #{superNodeLimit} AND COUNT(u1.flag = 0 and r1.referee_id = r.user_id AND uw1.locked_coin >= #{tinyLimit} OR r1.referee_id = r.user_id AND u1.flag > 1 OR NULL) >= #{numLimit}"
    })
    Boolean isPartner(@Param("userId")String userId, @Param("coinType") String coinType, @Param("tinyLimit") String tinyLimit, @Param("strongLimit") String strongLimit, @Param("superNodeLimit") String superNodeLimit, @Param("numLimit") int numLimit);
}
